package com.kevin.sql;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;

import com.kevin.enums.DBType;
import com.kevin.utils.string.KnStringUtils;

public class SqlCompatible {
	/**
	 * 数据库兼容处理
	 * @param text
	 * @param dbType
	 * @return
	 */
	public static String Format(String text, DBType dbType){
		if(StringUtils.isEmpty(text)) return text;
		if(text.indexOf("=") > -1){
			text = formatPara(text, dbType);
		}
		if(text.contains("[#") && text.contains("]")){
			text = formatLeft(text, dbType);
			text = formatRight(text, dbType);
			text = formatContact(text, dbType);
			text = formatIsNull(text, dbType);
			text = formatUUID(text, dbType);
			text = formatTrueFalseAscDesc(text, dbType);		
			text = formatLen(text, dbType);
			text = formatDateDiff(text, dbType);//日期替换前
			text = formatDate(text, dbType, SqlValue.YEAR, "Year");
            text = formatDate(text, dbType, SqlValue.MONTH, "Month");
            text = formatDate(text, dbType, SqlValue.DAY, "Day");
			text = formatGetDate(text, dbType);
			text = formatCharIndex(text, dbType);
			text = formatCaseWhen(text, dbType);			
		}
		return text;
	}
	
	
	private static String formatLeft(String text, DBType dbType)
    {
        switch (dbType)
        {
            //substr(MAX(SheetId),1,4)) IS NULL THEN 0 ELSE substr(MAX(SheetId)length(MAX(SheetId))-4,4) 
            case ORACLE:
                int index =StringUtils.indexOfIgnoreCase(text, SqlValue.LEFT);//left(a,4) =>to_char(substr(a,1,4))
                if (index > -1)
                {
                    do
                    {
                        index = text.indexOf('(', index);
                        int end = text.indexOf(',', index);
                        int end2 = text.indexOf(')', end + 1);
                        text = KnStringUtils.inserString(text, ")", end2);
                        text = KnStringUtils.inserString(text,"1," ,end + 1);
                        index = StringUtils.indexOfIgnoreCase(text,SqlValue.LEFT,end);//寻找还有没有第二次出现的函数字段
                    }
                    while (index > -1);
                    return replace(text, SqlValue.LEFT, "to_char(substr");
                }
                return text;
            default:
                return replace(text, SqlValue.LEFT, "Left");
        }
    }
	
	private static String formatRight(String text, DBType dbType)
    {
        switch (dbType)
        {
            case ORACLE:
                int index = StringUtils.indexOfIgnoreCase(text,SqlValue.RIGHT);//right(a,4) => to_char(substr(a,length(a)-4,4))
                if (index > -1)
                {
                    do
                    {
                        ////substr(MAX(SheetId),1,4)) IS NULL THEN 0 ELSE substr(MAX(SheetId)length(MAX(SheetId))-4,4) 
                        index = text.indexOf('(', index);
                        int end = text.indexOf(',', index);
                        String key = text.substring(index + 1, end - index - 1);//找到 a
                        int end2 = text.indexOf(')', end + 1);
                        String key2 = text.substring(end + 1, end2 - end - 1);//找到b
                        text = KnStringUtils.inserString(text,")",end2);
                        text = KnStringUtils.inserString(text,"length(" + key + ")+1-" + key2 + ",",end + 1);//
                        index = StringUtils.indexOfIgnoreCase(text,SqlValue.RIGHT, end);//寻找还有没有第二次出现的函数字段
                    }
                    while (index > -1);
                    return replace(text, SqlValue.RIGHT, "to_char(substr");
                }
                return text;
            default:
                return replace(text, SqlValue.RIGHT, "Right");
        }
    }
	
	private static String formatContact(String text, DBType dbType)
     {
         switch (dbType)
         {
             case ORACLE:
                 return replace(text, SqlValue.CONTACT, "||");
             default:
                 return replace(text, SqlValue.CONTACT, "+");
         }
     }
	
	private static String formatIsNull(String text, DBType dbType)
    {
        switch (dbType)
        {
            case ACCESS:
                int index = StringUtils.indexOfIgnoreCase(text, SqlValue.ISNULL);//isnull  (isnull(aaa),'3,3')   iif(isnull   (aaa),333,aaa)
                if (index > -1)
                {

                    do
                    {
                        index = text.indexOf('(', index);
                        int end = text.indexOf(',', index);
                        String key = text.substring(index + 1, end - index - 1);//找到 aaa
                        text = KnStringUtils.inserString(text, ")", end);//
                        end = text.indexOf(')', end + 3);
                        text = KnStringUtils.inserString(text, "," + key,end);
                        index = StringUtils.indexOfIgnoreCase(text,SqlValue.ISNULL, end);//寻找还有没有第二次出现的函数字段
                    }
                    while (index > -1);
                    return replace(text, SqlValue.ISNULL, "iif(isnull");
                }
                break;
            case SQLITE:
            case MYSQL:
                return replace(text, SqlValue.ISNULL, "IfNull");
            case ORACLE:
                return replace(text, SqlValue.ISNULL, "NVL");
            case MSSQL:
            case SYBASE:
            default:
                return replace(text, SqlValue.ISNULL, "IsNull");

        }
        return text;
    }
	
	private static String formatUUID(String text, DBType dbType)
    {
        switch (dbType)
        {
            case ACCESS:
                return replace(text, SqlValue.UUID, "GenGUID()");
            case MYSQL:
                return replace(text, SqlValue.UUID, "UUID()");
            case MSSQL:
            case SYBASE:
                return replace(text, SqlValue.UUID, "newid()");
            case ORACLE:
                return replace(text, SqlValue.UUID, "SYS_GUID()");
            case SQLITE:
                return replace(text, SqlValue.UUID, "");
		default:
			return text;
        }
    }

    private static String formatPara(String text, DBType dbType)
    {
        switch (dbType)
        {
            case MYSQL:
                return text.replace("=:?", "=?");
            case ORACLE:
                return text.replace("=:?", "=:");
            default:
                return text.replace("=:?", "=@");
        }
    }
	
    private static String formatTrueFalseAscDesc(String text, DBType dbType)
    {
        switch (dbType)
        {
            case ACCESS:
                text = replace(text, SqlValue.TRUE, "true");
                text = replace(text, SqlValue.FALSE, "false");
                text = replace(text, SqlValue.DESC, "asc");
                return replace(text, SqlValue.ASC, "desc");
            default:
                text = replace(text, SqlValue.TRUE, "1");
                text = replace(text, SqlValue.FALSE, "0");
                text = replace(text, SqlValue.DESC, "desc");
                return replace(text, SqlValue.ASC, "asc");
        }
    }
    
    private static String formatLen(String text, DBType dbType)
    {
        switch (dbType)//处理函数替换
        {
            case ACCESS:
            case MSSQL:
                text = replace(text, SqlValue.LEN, "len");
                return replace(text, SqlValue.SUBSTRING, "substring");
            case ORACLE:
            case SQLITE:
                text = replace(text, SqlValue.LEN, "length");
                return replace(text, SqlValue.SUBSTRING, "substr");
            case MYSQL:
                text = replace(text, SqlValue.LEN, "char_length");
                return replace(text, SqlValue.SUBSTRING, "substring");
            case SYBASE:
                text = replace(text, SqlValue.LEN, "datalength");
                return replace(text, SqlValue.SUBSTRING, "substring");
		default:
			return text;
        }
    }
	
    private static String getFormatDateKey(DBType dbType, String key)
    {
        switch (dbType)
        {
            case SQLITE:
                switch (key)
                {
                    case SqlValue.YEAR:
                        return "'%Y',";
                    case SqlValue.MONTH:
                        return "'%m',";
                    case SqlValue.DAY:
                        return "'%d',";
                }
                break;
            case SYBASE:
                switch (key)
                {
                    case SqlValue.YEAR:
                        return "yy,";
                    case SqlValue.MONTH:
                        return "mm,";
                    case SqlValue.DAY:
                        return "dd,";
                }
                break;
            default:
                switch (key)
                {
                    case SqlValue.YEAR:
                        return ",'yyyy'";
                    case SqlValue.MONTH:
                        return ",'MM'";
                    case SqlValue.DAY:
                        return ",'dd'";
                }
                break;
        }
        return StringUtils.EMPTY;
    }

    private static String formatDate(String text, DBType dbType, String key, String func)
    {
        int index = StringUtils.indexOfIgnoreCase(text, key);//[#year](字段)
        if (index > -1)//存在[#year]函数
        {
            String format = getFormatDateKey(dbType, key);
            int found = 0;
            switch (dbType)
            {
                case ORACLE:
                    do
                    {
                        text = KnStringUtils.inserString(text, "_", index + 2);//[#_year](字段)
                        found = text.indexOf(')', index + 4);//从[#_year(字段)]找到 ')'的位置
                        text = KnStringUtils.inserString(text, format,found);//->[#_year](字段,'yyyy')
                        index = StringUtils.indexOfIgnoreCase(text,key);//寻找还有没有第二次出现的函数字段
                    }
                    while (index > -1);
                    text = text.replace("#_", "#");
                    text = replace(text, key, "to_char");//[#year](字段,'yyyy')
                    break;
                case SQLITE:
                    do
                    {
                        text = KnStringUtils.inserString(text, "_",index + 2);//[#_year](字段)
                        found = text.indexOf('(', index + 4);//从[#_year(字段)]找到 '('的位置
                        text = KnStringUtils.inserString(text, format,found + 1);//->[#_year]('%Y',字段)
                        found = text.indexOf(')', found + 1);
                        text = KnStringUtils.inserString(text, " as int)",found + 1);
                        index = StringUtils.indexOfIgnoreCase(text,key);//寻找还有没有第二次出现的函数字段
                    }
                    while (index > -1);
                    text = text.replace("#_", "#");
                    text = replace(text, key, "cast(strftime");//cast(strftime('%Y', UpdateTime) as int) [%Y,%m,%d]
                    break;
                case SYBASE:
                    text = replace(text, key + "(", "datepart(" + format);
                    //// [#YEAR](getdate())  datepart(mm,getdate()) datepart(mm,getdate()) datepart(mm,getdate())
                    break;
                default:
                    text = replace(text, key, func);
                    break;
            }
        }
        return text;
    }
    
    private static String formatGetDate(String text, DBType dbType)
    {
        switch (dbType)
        {
            case ACCESS:
            case MYSQL:
                return replace(text, SqlValue.GETDATE, "now()");
            case MSSQL:
            case SYBASE:
                return replace(text, SqlValue.GETDATE, "getdate()");
            case ORACLE:
                return replace(text, SqlValue.GETDATE, "current_date");
            case SQLITE:
                return replace(text, SqlValue.GETDATE, "datetime('now','localtime')");
            case TXT:
            case XML:
                return replace(text, SqlValue.GETDATE, "'" +  new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + "'");
		default:
			return text;
        } 
    }
    
    private static String formatCharIndex(String text, DBType dbType)
    {
        String key = SqlValue.CHARINDEX;
        //select [#charindex]('ok',xxx) from xxx where [#charindex]('ok',xx)>0
        int index = StringUtils.indexOfIgnoreCase(text, key);
        if (index > -1)//存在charIndex函数
        {
            switch (dbType)
            {
                case ACCESS:
                case ORACLE:
                    int found = 0;
                    String func = StringUtils.EMPTY;
                    do
                    {
                        int start = index + key.length();
                        text = KnStringUtils.inserString(text, "_", index + 2);//select [#_charindex]('ok',xxx) from xxx where [#charindex]('ok',xx)>0
                        found = text.indexOf(')', index + 4);
                        func = text.substring(start + 2, found - start - 2);
                        String[] funs = func.split(",");
                        text = KnStringUtils.removeString(text, start + 2, found - start - 2);//移除//select [#_charindex]() from xxx where [#charindex]('ok',xx)>0
                        text = KnStringUtils.inserString(text, funs[1] + "," + funs[0],start + 2);
                        index = StringUtils.indexOfIgnoreCase(text,key);
                    }
                    while (index > -1);
                    text = text.replace("#_", "#");
                    return replace(text, key, "instr");
                case MYSQL:
                    return replace(text, key, "locate");
                case MSSQL:
                case SYBASE:
                    return replace(text, key, "charindex");
                case SQLITE:
                    return replace(text, key, "charindex");
			default:
				break;
            }
        }
        return text;
    }
    
    private static String formatDateDiff(String text, DBType dbType)
    {
        String key = SqlValue.DATEDIFF;
        //select [#DATEDIFF](aa,'bb','cc') from xxx where [#DATEDIFF](aa,'bb','cc')>0
        int index = StringUtils.indexOfIgnoreCase(text, key);
        if (index > -1)//'yyyy','q','m','y','d','ww','hh/h','n','s'
        {
            String[] keys = new String[] { "yyyy", "q", "m", "y", "d", "h", "ww", "n", "s" };//"hh/h"
            switch (dbType)
            {
                case ACCESS:
                case ORACLE:
                    for (String key1 : keys)
                    {
                        text = text.replace("[#" + key1 + "]", "'" + key1 + "'");
                    }
                    break;
                case MSSQL:
                case SYBASE:
                    text = text.replace("[#h]", "hh");
                    for (String key2 : keys)
                    {
                        text = text.replace("[#" + key2 + "]", key2);
                    }
                    break;
                case MYSQL://和mssql/access参数相反
                    for (String key2 : keys)
                    {
                        text = text.replace("[#" + key2 + "],", StringUtils.EMPTY);
                    }
                    text = text.replace("()", "$,$");
                    int found = 0;
                    String func = StringUtils.EMPTY;
                    do
                    {
                        int start = index + key.length();
                        text = KnStringUtils.inserString(text, "_", index + 2);//select [#_DateDiff](time1,time2()) from xxx where [#DateDiff](time1,time2())>0
                        found = text.indexOf(')', index + 4);
                        func = text.substring(start + 2, found - start - 2);
                        String[] funs = func.split(",");
                        text = KnStringUtils.removeString(text, start + 2, found - start - 2);//移除//select [#_DateDiff() from xxx where [#DateDiff](time1,time2)>0
                        text = KnStringUtils.inserString(text, funs[1] + "," + funs[0],start + 2);
                        index = StringUtils.indexOfIgnoreCase(text,key);
                    }
                    while (index > -1);
                    text = text.replace("#_", "#").replace("$,$", "()");
                    break;
                case SQLITE:
                    found = 0;
                    func = StringUtils.EMPTY;
                    do
                    {
                        int start = index + key.length();
                        text = KnStringUtils.inserString(text, "_",index + 2);//[#_DateDiff]([#d],startTime',endTime)
                        found = text.indexOf(')', index + 4);
                        func = text.substring(start + 2, found - start - 2);//[#d],startTime',endTime
                        String[] funs = func.split(",");
                        text = KnStringUtils.removeString(text,start + 2, found - start - 2);//移除[#_DateDiff]()
                        text = KnStringUtils.inserString(text, "julianday(" + funs[2] + ")-julianday(" + funs[1] + ")",start + 2);
                        index = StringUtils.indexOfIgnoreCase(text,key);//寻找还有没有第二次出现的函数字段
                    }
                    while (index > -1);
                    text = text.replace("#_", "#");
                    text = replace(text, key, StringUtils.EMPTY);
                    break;
			default:
				break;
            }
        }
        return replace(text, key, "DateDiff");
    }
    
    private static String formatCaseWhen(String text, DBType dbType)
    {
        //CASE when languageID=1 THEN 1000 ELSE 10 End

        switch (dbType)
        {
            case MSSQL:
            case ORACLE:
            case MYSQL:
            case SQLITE:
            case SYBASE:
                if (StringUtils.indexOfIgnoreCase(text, SqlValue.CASE) > -1 || StringUtils.indexOfIgnoreCase(text,SqlValue.CASEWHEN) > -1)
                {
                    text = replace(text, SqlValue.CASE, "Case");
                    text = replace(text, SqlValue.CASEWHEN, "Case When");
                    text = replace(text, "[#WHEN]", "when");
                    text = replace(text, "[#THEN]", "then");
                    text = replace(text, "[#ELSE]", "else");
                    text = replace(text, "[#END]", "end");
                }
                break;
            case ACCESS:
                if (StringUtils.indexOfIgnoreCase(text,SqlValue.CASE) > -1)
                {
                    text = replace(text, SqlValue.CASE, StringUtils.EMPTY);
                    text = replace(text, " [#WHEN] ", "iif(");
                    text = replace(text, " [#THEN] ", ",");
                    text = replace(text, " [#ELSE] ", ",");
                    text = replace(text, " [#END]", ")");
                }
                else if (StringUtils.indexOfIgnoreCase(text,SqlValue.CASEWHEN) > -1)
                {
                    text = replace(text, SqlValue.CASEWHEN, "SWITCH(");
                    text = replace(text, "[#THEN]", ",");
                    text = replace(text, "[#ELSE]", "TRUE,");
                    text = replace(text, "[#END]", ")");
                }
                break;
		default:
			break;
        }

        return text;
    }
    
    //忽略大小写的替换。
    private static String replace(String text, String oldValue, String newValue)
    {
        oldValue = oldValue.replace("[", "\\[").replace("]", "\\]");       
        return Pattern.compile(oldValue, Pattern.CASE_INSENSITIVE).matcher(text).replaceAll(newValue);
    }
}
